package com.sun.officeoa.dao;

import com.sun.officeoa.bean.Employee;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;
@Repository
public class EmployeeDao {

    @Resource
    private JdbcTemplate template;


    public int updateEmpStatusById(int id, int status) {
        String sql = "UPDATE employee set status=? WHERE employeeid=?";
        return template.update(sql, status, id);
    }

    public List<Employee> getUnApproveaccount() {
        List<Employee> users = null;
        String sql ="SELECT * FROM employee WHERE status=0 AND role =2";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
        users = template.query(sql,rowMapper);
        return (users != null && users.size() > 0) ? users : null;
    }

    public List<Employee> getEmpByDepId(int depId) {
        List<Employee> users = null;
        String sql ="SELECT * FROM employee WHERE status=1 AND departmentid=?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
        users = template.query(sql,rowMapper,new Object[]{depId});
        return (users != null && users.size() > 0) ? users : null;
    }

    public Employee login(String username, String password) {
        List<Employee> users = null;
        String sql ="SELECT * FROM employee WHERE username=? AND password=?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
        users = template.query(sql,rowMapper,new Object[]{username,password});
        return (users != null && users.size() > 0) ? users.get(0) : null;
    }

    public List<Employee> getEmpByMeetingId (int meegingId){
        List<Employee> employeeList = null;
        String sql = "SELECT * FROM employee WHERE employeeid IN(SELECT employeeid FROM meetingparticipants WHERE meetingid=?)";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
        employeeList = template.query(sql,rowMapper,new Object[]{meegingId});
        return (employeeList != null && employeeList.size() > 0) ? employeeList : null;
    }

    public int insert(Employee employee){
        if (!isUsernameExists(employee.getUsername())) {
            return -1;
        }
        String sql ="INSERT INTO employee(employeename,username,phone,email,status,departmentid,password,role) VALUES (?,?,?,?,?,?,?,?)";
        return template.update(sql,new Object[]{employee.getEmployeename(),employee.getUsername(),employee.getPhone(),employee.getEmail(),"0",employee.getDepartmentid(),employee.getPassword(),"2"});
    }

    public boolean isUsernameExists(String username){
        boolean isYesNo = true;
        String sql = "SELECT * from employee WHERE username=?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
       List<Employee> employeeList = template.query(sql,rowMapper,new Object[]{username});
       if(employeeList.size()>0 && employeeList!=null){
           isYesNo = false;
       }
        return isYesNo;
    }

   public List<Employee> searchEmp(String employeename, String username, int status, int page, int count){
       List<Employee> employeeList = null;
       String sql = "";
       RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
       if (employeename != null && !"".equals(employeename) && username != null && !"".equals(username)) {
           sql = "SELECT * FROM employee WHERE status=? AND role =2 and employeename=? and username=? limit ?,?";
           employeeList = template.query(sql,rowMapper,new Object[]{status,employeename,username,(page - 1) * count,count});
           return (employeeList != null && employeeList.size() > 0) ? employeeList : null;
       }
       if (username != null && !"".equals(username)) {
           sql = "SELECT * FROM employee WHERE status=? AND role =2 and username=? limit ?,?";
           employeeList = template.query(sql,rowMapper,new Object[]{status,username,(page - 1) * count,count});
           return (employeeList != null && employeeList.size() > 0) ? employeeList : null;
       }
       if(employeename != null && !"".equals(employeename)){
           sql = "SELECT * FROM employee WHERE status=? AND role =2 and employeename=? limit ?,?";
           employeeList = template.query(sql,rowMapper,new Object[]{status,employeename,(page - 1) * count,count});
           return (employeeList != null && employeeList.size() > 0) ? employeeList : null;
       }
       sql = "SELECT * FROM employee WHERE status=? AND role =2 limit ?,?";
       employeeList = template.query(sql,rowMapper,new Object[]{status,(page - 1) * count,count});
       return (employeeList != null && employeeList.size() > 0) ? employeeList : null;
   }

   public int getCount(String employeename, String username, int status){
        List<Employee> employeeList = null;
        String sql ="";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
        if (employeename != null && !"".equals(employeename) && username != null && !"".equals(username)) {
           sql = "SELECT * FROM employee WHERE status=? AND role =2 and employeename=? and username=?";
           employeeList = template.query(sql,rowMapper,new Object[]{status,employeename,username});
           return (employeeList != null && employeeList.size() > 0) ? employeeList.size() : 0;
        }
        if (username != null && !"".equals(username)) {
           sql = "SELECT * FROM employee WHERE status=? AND role =2 and username=?";
           employeeList = template.query(sql,rowMapper,new Object[]{status,username});
           return (employeeList != null && employeeList.size() > 0) ? employeeList.size() : 0;
        }
        if(employeename != null && !"".equals(employeename)){
           sql = "SELECT * FROM employee WHERE status=? AND role =2 and employeename=?";
           employeeList = template.query(sql,rowMapper,new Object[]{status,employeename});
           return (employeeList != null && employeeList.size() > 0) ? employeeList.size() : 0;
        }
       sql = "SELECT * FROM employee WHERE status=? AND role =2";
       employeeList = template.query(sql,rowMapper,new Object[]{status});
       return (employeeList != null && employeeList.size() > 0) ? employeeList.size() : 0;
   }

   public int updateUser(int emId,String password){
        String sql = "UPDATE employee SET password =? where employeeid=?";
        return template.update(sql,new Object[]{password,emId});
   }



}
